package database

import (
	"bufio"
	"database/sql"
	"fmt"
	"strconv"
	"strings"

	_ "github.com/go-sql-driver/mysql"
	"x-admin-generator/internal/types"
)

// Connect 连接数据库
func Connect(config *types.DatabaseConfig) (*sql.DB, error) {
	// 构建DSN
	charset := config.Charset
	if charset == "" {
		charset = "utf8mb4"
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s&parseTime=True&loc=Local",
		config.Username, config.Password, config.Host, config.Port, config.Database, charset)

	// 根据驱动类型选择数据库
	driver := config.Driver
	if driver == "" {
		driver = "mysql"
	}

	db, err := sql.Open(driver, dsn)
	if err != nil {
		return nil, fmt.Errorf("打开数据库连接失败: %w", err)
	}

	if err := db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("数据库连接测试失败: %w", err)
	}

	fmt.Printf("✅ 数据库连接成功: %s@%s:%d/%s\n",
		config.Username, config.Host, config.Port, config.Database)

	return db, nil
}

// GetTables 获取数据库中的所有表
func GetTables(db *sql.DB, database string) ([]types.TableInfo, error) {
	query := `
		SELECT TABLE_NAME, TABLE_COMMENT
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'
		ORDER BY TABLE_NAME
	`

	rows, err := db.Query(query, database)
	if err != nil {
		return nil, fmt.Errorf("查询表列表失败: %w", err)
	}
	defer rows.Close()

	var tables []types.TableInfo
	for rows.Next() {
		var table types.TableInfo
		var comment sql.NullString

		if err := rows.Scan(&table.Name, &comment); err != nil {
			return nil, fmt.Errorf("扫描表信息失败: %w", err)
		}

		if comment.Valid {
			table.Comment = comment.String
		}

		tables = append(tables, table)
	}

	return tables, nil
}

// GetTableInfo 获取表的详细信息
func GetTableInfo(db *sql.DB, database, tableName string) (*types.TableInfo, error) {
	// 获取表注释
	var tableComment sql.NullString
	commentQuery := `
		SELECT TABLE_COMMENT
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
	`
	db.QueryRow(commentQuery, database, tableName).Scan(&tableComment)

	// 获取列信息
	query := `
		SELECT
			COLUMN_NAME,
			DATA_TYPE,
			IS_NULLABLE,
			COLUMN_KEY,
			COLUMN_DEFAULT,
			EXTRA,
			COLUMN_COMMENT
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
		ORDER BY ORDINAL_POSITION
	`

	rows, err := db.Query(query, database, tableName)
	if err != nil {
		return nil, fmt.Errorf("查询列信息失败: %w", err)
	}
	defer rows.Close()

	var columns []types.TableColumn
	for rows.Next() {
		var col types.TableColumn
		var nullable string

		if err := rows.Scan(
			&col.Name,
			&col.Type,
			&nullable,
			&col.Key,
			&col.Default,
			&col.Extra,
			&col.Comment,
		); err != nil {
			return nil, fmt.Errorf("扫描列信息失败: %w", err)
		}

		col.Nullable = nullable == "YES"
		columns = append(columns, col)
	}

	tableInfo := &types.TableInfo{
		Name:    tableName,
		Columns: columns,
	}

	if tableComment.Valid {
		tableInfo.Comment = tableComment.String
	}

	return tableInfo, nil
}

// GetConfigInteractive 交互式获取数据库配置
func GetConfigInteractive(scanner *bufio.Scanner) *types.DatabaseConfig {
	config := &types.DatabaseConfig{}

	fmt.Print("数据库主机 (默认: localhost): ")
	if scanner.Scan() {
		host := strings.TrimSpace(scanner.Text())
		if host == "" {
			host = "localhost"
		}
		config.Host = host
	}

	fmt.Print("数据库端口 (默认: 3306): ")
	if scanner.Scan() {
		portStr := strings.TrimSpace(scanner.Text())
		if portStr == "" {
			config.Port = 3306
		} else {
			if port, err := strconv.Atoi(portStr); err == nil {
				config.Port = port
			} else {
				fmt.Printf("❌ 无效的端口号: %s\n", portStr)
				return nil
			}
		}
	}

	fmt.Print("数据库用户名: ")
	if scanner.Scan() {
		config.Username = strings.TrimSpace(scanner.Text())
		if config.Username == "" {
			fmt.Println("❌ 用户名不能为空")
			return nil
		}
	}

	fmt.Print("数据库密码: ")
	if scanner.Scan() {
		config.Password = strings.TrimSpace(scanner.Text())
	}

	fmt.Print("数据库名称: ")
	if scanner.Scan() {
		config.Database = strings.TrimSpace(scanner.Text())
		if config.Database == "" {
			fmt.Println("❌ 数据库名称不能为空")
			return nil
		}
	}

	return config
}

// ParseTableSelection 解析表选择
func ParseTableSelection(selection string, tables []types.TableInfo) []types.TableInfo {
	var selectedTables []types.TableInfo
	selections := strings.Split(selection, ",")

	for _, sel := range selections {
		sel = strings.TrimSpace(sel)
		if sel == "" {
			continue
		}

		// 尝试按序号解析
		if index, err := strconv.Atoi(sel); err == nil {
			if index >= 1 && index <= len(tables) {
				selectedTables = append(selectedTables, tables[index-1])
				continue
			}
		}

		// 尝试按表名解析
		for _, table := range tables {
			if strings.EqualFold(table.Name, sel) {
				selectedTables = append(selectedTables, table)
				break
			}
		}
	}

	return selectedTables
}

// ConvertMySQLTypeToGo 将MySQL类型转换为Go类型
func ConvertMySQLTypeToGo(mysqlType string) string {
	mysqlType = strings.ToLower(mysqlType)

	switch {
	case strings.Contains(mysqlType, "int"):
		if strings.Contains(mysqlType, "unsigned") {
			return "uint"
		}
		return "int"
	case strings.Contains(mysqlType, "varchar"), strings.Contains(mysqlType, "char"):
		return "string"
	case strings.Contains(mysqlType, "text"):
		return "string"
	case strings.Contains(mysqlType, "decimal"), strings.Contains(mysqlType, "numeric"):
		return "decimal"
	case strings.Contains(mysqlType, "float"), strings.Contains(mysqlType, "double"):
		return "float64"
	case strings.Contains(mysqlType, "bool"), strings.Contains(mysqlType, "tinyint(1)"):
		return "bool"
	case strings.Contains(mysqlType, "datetime"), strings.Contains(mysqlType, "timestamp"):
		return "time.Time"
	case strings.Contains(mysqlType, "date"):
		return "time.Time"
	case strings.Contains(mysqlType, "time"):
		return "time.Time"
	default:
		return "string"
	}
}